package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import controllers.ExceptionsTypes;

import person.Customer;
import person.Employee;
import person.User;

public class UserWrapper{
	
	public static User getUser(String userName, String password, boolean closeConnection) throws Exception{
		User user = null;
		String query1 = "SELECT id_user, user_type "  +
                "FROM " + DBWrapper.getDbname() + ".user " +
                "WHERE user_name = '"+userName+"' AND password = '"+password+"'";
		
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query1);
	        if(rs.next()){
		        String userType = rs.getString("user_type");
		        long id = rs.getInt("id_user");
		        if(userType.equalsIgnoreCase("customer")){
		        	user = CustomerWrapper.getCustomer(id, false);
		        }
		        else if(userType.equalsIgnoreCase("employee")){
		        	user = EmployeeWrapper.getEmployee(id, false);
		        }
		        else
		        	throw new Exception(ExceptionsTypes.CORRUPT_DB_STATE.name());
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
		return user;
	}
	
	public static void insertUserCustomer(String userName, String password, Customer user, boolean closeConnection) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		int rows = 0;
		Customer c = (Customer) user;
		Customer found=null;
		try{
			found=CustomerWrapper.getCustomer(c.getTaxCode(),false);
		}catch(Exception ex){
			if(ex.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
				CustomerWrapper.insertCustomer(false, c);
			else
				throw ex;
		}
	    rows = stmt.executeUpdate(
	    		"INSERT INTO " + DBWrapper.getDbname() + ".user " +
	    		"VALUES('" + userName + "', '" + 
	    		password +"', '" +
	    		found.getId() + "', 'customer')");
	    
	    if(rows==0)
			throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	public static void insertUserEmployee(String userName, String password, Employee user, boolean closeConnection) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
			int rows = 0;
				try{
					EmployeeWrapper.getEmployee(user.getTaxCode(),false);
				}catch(Exception ex){
					if(ex.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
						EmployeeWrapper.insertEmployee(false, user);
						rows = stmt.executeUpdate(
			    		"INSERT INTO " + DBWrapper.getDbname() + ".user " +
			    		"VALUES('" + userName + "', '" + 
			    		password +"', '" +
			    		user.getId() + "', 'employee')");
				}
	
			if(rows==0)
				throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	  
	}
	//TODO Usarlo quando si licenzia un dipendente
	public static void deleteUser(boolean closeConnection, User user) throws Exception{
		String query = null;
		if(user instanceof Customer){
			query = "DELETE "  +
					"FROM "+ DBWrapper.getDbname() +".user " +
					"WHERE id_user = '"+((Customer)user).getId()+"' AND user_type ='customer'";
			//PersonWrapper.deleteCustomer(false, (Customer)user);
		}
		else if(user instanceof Employee){
			query = "DELETE * "  +
		    		"FROM "+ DBWrapper.getDbname() +".user " +
		    		"WHERE id_user = '"+((Employee)user).getId()+"' AND user_type ='employee'";
			//PersonWrapper.deleteEmployee(false, (Employee)user);
		}
		else
			throw new Exception(ExceptionsTypes.DIPENDENCE_ERROR.name());
    	Statement stmt = DBWrapper.getConnection().createStatement();
        int rows = stmt.executeUpdate(query);
	    if(rows>1){
	       	throw new Exception(ExceptionsTypes.DIPENDENCE_ERROR.name());
	    }else if(rows == 0)
	       	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    stmt.close();
	    if(closeConnection)
	      	DBWrapper.getConnection().close();
	}
	
	public static void modifyPassword(String userName, String newPassword, boolean closeConnection) throws SQLException, Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		int rows = stmt.executeUpdate(
		        "UPDATE "+ DBWrapper.getDbname() +".user" +
		        " SET password='" + newPassword +
		        "' WHERE user_name='" + userName + "'");
		
		if(rows == 0)
			throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        if(closeConnection)
        	DBWrapper.getConnection().close();
        
	}

	public static String getuserName(User user,boolean closeConnection) throws Exception {
		Statement stmt = DBWrapper.getConnection().createStatement();
		String sql="select * from "+DBWrapper.getDbname()+".user where id_user="+user.getId()+";";
		ResultSet rs = stmt.executeQuery(sql);
		rs.next();
		String userName=rs.getString("user_name");
		if (userName.equals("")|| userName==null)throw new Exception(ExceptionsTypes.NOT_FOUND.name());
		if(closeConnection)DBWrapper.getConnection().close();
		return userName;
	}
	public static boolean exists(String userName,boolean closeConnection) throws Exception{
		Statement stmt=DBWrapper.getConnection().createStatement();
		ResultSet rs=stmt.executeQuery("SELECT Count(*) FROM "+DBWrapper.getDbname()+".user WHERE `user_name`=\""+userName+"\";");
		rs.next();
		boolean ret= rs.getInt(1)>0;
		if(closeConnection)stmt.close();
		return ret;
		
	}
}
